#![allow(unused_variables)]
use sqlx::Error;
use super::CountNum;
use super::PermissionBean;
use super::RoleBean;
use super::RoleQueryRequest;
use super::Table;

impl<'c> Table<'c, PermissionBean> {
pub async fn list_active_role_permissions_using_role_id(&self,role_id:i64) ->Result<Vec<PermissionBean>, Error> {
		let result = sqlx::query_as(
			r#"select distinct cs_permissions.id,cs_permissions.perm_name,cs_permissions.active from cs_permissions,cs_role_permission where cs_permissions.id = cs_role_permission.permission_id and cs_role_permission.role_id = ? and cs_permissions.active = true order by cs_permissions.perm_name asc "#
		)
		.bind(role_id)
		.fetch_all(&(&*self.pool).clone().unwrap())
		.await;
		return result;
}

}
impl<'c> Table<'c, RoleBean> {
pub async fn activate_role(&self,id:i64) ->Result<u64, Error> {
		let result = sqlx::query(
	r#"update cs_roles set active = true where id = ? "#
		)
		.bind(id)
		.execute(&(&*self.pool).clone().unwrap())
		.await;
		match result {
			Ok(result) => {
				Ok(result.rows_affected())
			},
			Err(err) => {
				Err(err)
			}
		}
}

pub async fn add_role(&self,role:RoleBean) ->Result<u64, Error> {
		let result = sqlx::query(
		r#"insert into cs_roles ( role_name,active) values (?,?)"#
		)
		.bind(role.role_name)
		.bind(role.active)
		.execute(&(&*self.pool).clone().unwrap())
		.await;
		match result {
			Ok(result) => {
				Ok(result.rows_affected())
			},
			Err(err) => {
				Err(err)
			}
		}
}

pub async fn assign_permission_to_role(&self,role_id:i64,permission_id:i64) ->Result<u64, Error> {
		let result = sqlx::query(
			r#"insert into cs_role_permission ( role_id,permission_id) values (?,?)"#
		)
		.bind(role_id)
		.bind(permission_id)
		.execute(&(&*self.pool).clone().unwrap())
		.await;
		match result {
			Ok(result) => {
				Ok(result.rows_affected())
			},
			Err(err) => {
				Err(err)
			}
		}
}

pub async fn count_active_role_records(&self) ->Result<CountNum, Error> {
		let result = sqlx::query_as(
		r#"select count(*) as countNum from cs_roles where active = true"#
		)
		.fetch_one(&(&*self.pool).clone().unwrap())
		.await;
		return result;
}

pub async fn count_search_roles_by_fields_records(&self,role_query_request:RoleQueryRequest) ->Result<CountNum, Error> {
		let mut query = r#"select count(*) as countNum from cs_roles where 1=1 "#.to_string();
		let role_name = role_query_request.role_name.unwrap_or_default();
		if !role_name.is_empty() {
			let query_role_name  = format!("{}{}{}"," and role_name like '%",role_name,"%' ");
			query = format!("{} {}", query, query_role_name);
		}
		let active = role_query_request.active.unwrap_or_default();
		if !active.is_empty() && (active=="true" || active =="false"){
			let query_active  = format!("{}{}{}"," and active = ",active," ");
			query = format!("{} {}", query, query_active);
		}
		let result = sqlx::query_as(&query)
		.fetch_one(&(&*self.pool).clone().unwrap())
		.await;
		return result;
}

pub async fn delete_role(&self,id:i64) ->Result<u64, Error> {
		let result = sqlx::query(
	r#"delete from cs_roles where id = ? "#
		)
		.bind(id)
		.execute(&(&*self.pool).clone().unwrap())
		.await;
		match result {
			Ok(result) => {
				Ok(result.rows_affected())
			},
			Err(err) => {
				Err(err)
			}
		}
}

pub async fn find_role_by_id(&self,id:i64) ->Result<RoleBean, Error> {
	let result = sqlx::query_as(
	r#"select id,role_name,active from cs_roles where id = ? "#
		)
		.bind(id)
		.fetch_one(&(&*self.pool).clone().unwrap())
		.await;
		return result;
}

pub async fn find_role_by_role_name(&self,role_name:String) ->Result<RoleBean, Error> {
	let result = sqlx::query_as(
	r#"select id,role_name,active from cs_roles where role_name = ?;"#
		)
		.bind(role_name)
		.fetch_one(&(&*self.pool).clone().unwrap())
		.await;
		return result;
}

pub async fn list_active_roles(&self) ->Result<Vec<RoleBean>, Error> {
		let result = sqlx::query_as(
	r#"select id,role_name,active from cs_roles where active = true order by role_name asc"#
		)
		.fetch_all(&(&*self.pool).clone().unwrap())
		.await;
		return result;
}

pub async fn list_all_roles(&self) ->Result<Vec<RoleBean>, Error> {
		let result = sqlx::query_as(
	r#"select id,role_name,active from cs_roles"#
		)
		.fetch_all(&(&*self.pool).clone().unwrap())
		.await;
		return result;
}

pub async fn revoke_permission_from_role(&self,role_id:i64,permission_id:i64) ->Result<u64, Error> {
		let result = sqlx::query(
			r#"delete from cs_role_permission  where role_id = ? and permission_id = ? "#
		)
		.bind(role_id)
		.bind(permission_id)
		.execute(&(&*self.pool).clone().unwrap())
		.await;
		match result {
			Ok(result) => {
				Ok(result.rows_affected())
			},
			Err(err) => {
				Err(err)
			}
		}
}

pub async fn search_roles_by_fields(&self,role_query_request:RoleQueryRequest) ->Result<Vec<RoleBean>, Error> {
		let mut query = r#"select id,role_name,active from cs_roles where 1=1 "#.to_string();
		let role_name = role_query_request.role_name.unwrap_or_default();
		if !role_name.is_empty() {
			let query_role_name  = format!("{}{}{}"," and role_name like '%",role_name,"%' ");
			query = format!("{} {}", query, query_role_name);
		}
		let active = role_query_request.active.unwrap_or_default();
		if !active.is_empty() && (active=="true" || active =="false"){
			let query_active  = format!("{}{}{}"," and active = ",active," ");
			query = format!("{} {}", query, query_active);
		}
		let result = sqlx::query_as(&query)
		.fetch_all(&(&*self.pool).clone().unwrap())
		.await;
		return result;
}

pub async fn search_roles_by_fields_by_page(&self,role_query_request:RoleQueryRequest) ->Result<Vec<RoleBean>, Error> {
		let mut query = r#"select id,role_name,active from cs_roles where 1=1 "#.to_string();
		let role_name = role_query_request.role_name.unwrap_or_default();
		if !role_name.is_empty() {
			let query_role_name  = format!("{}{}{}"," and role_name like '%",role_name,"%' ");
			query = format!("{} {}", query, query_role_name);
		}
		let active = role_query_request.active.unwrap_or_default();
		if !active.is_empty() && (active=="true" || active =="false"){
			let query_active  = format!("{}{}{}"," and active = ",active," ");
			query = format!("{} {}", query, query_active);
		}
		let pagenum =  role_query_request.page.unwrap_or_default();
		let pagesize =  role_query_request.rows.unwrap_or_default();
		let start = (pagenum - 1) * pagesize;
		let limit = pagesize;
		query = format!("{} limit {} offset {} ", query, limit, start);
		let result = sqlx::query_as(&query)
		.fetch_all(&(&*self.pool).clone().unwrap())
		.await;
		return result;
}

pub async fn soft_delete_role(&self,id:i64) ->Result<u64, Error> {
		let result = sqlx::query(
		r#"update cs_roles set active = false where id = ? "#
		)
		.bind(id)
		.execute(&(&*self.pool).clone().unwrap())
		.await;
		match result {
			Ok(result) => {
				Ok(result.rows_affected())
			},
			Err(err) => {
				Err(err)
			}
		}
}

pub async fn toggle_role(&self,id:i64) ->Result<u64, Error> {
		let result = sqlx::query(
	r#"update cs_roles set active = not active where id = ? "#
		)
		.bind(id)
		.execute(&(&*self.pool).clone().unwrap())
		.await;
		match result {
			Ok(result) => {
				Ok(result.rows_affected())
			},
			Err(err) => {
				Err(err)
			}
		}
}

pub async fn update_role(&self,role:RoleBean) ->Result<u64, Error> {
		let result = sqlx::query(
		r#"update cs_roles set role_name = ? ,active = ? where id = ? "#
		)
		.bind(role.role_name)
		.bind(role.active)
		.bind(role.id)
		.execute(&(&*self.pool).clone().unwrap())
		.await;
		match result {
			Ok(result) => {
				Ok(result.rows_affected())
			},
			Err(err) => {
				Err(err)
			}
		}
}

pub async fn find_role_indexed_name(&self,short_name:String) ->Result<Vec<RoleBean>, Error> {
	let result = sqlx::query_as(
	r#"select id,role_name,active from cs_roles where role_name like concat(? ,'%') "#
	)
	.bind(short_name)
	.fetch_all(&(&*self.pool).clone().unwrap())
	.await;
		return result;
}

}
